package com.ccx.mytemplate.util;

import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
    public static Workbook data2Excel(List<String> headers, List<List<Object>> data) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // Create header row
        var headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.size(); i++) {
            var cell = headerRow.createCell(i);
            cell.setCellValue(headers.get(i));
        }
        // Create data rows
        for (int i = 0; i < data.size(); i++) {
            var dataRow = sheet.createRow(i + 1);
            List<Object> rowData = data.get(i);
            for (int j = 0; j < rowData.size(); j++) {
                var cell = dataRow.createCell(j);
                Object value = rowData.get(j);
                if (value instanceof Number) {
                    cell.setCellValue(((Number) value).doubleValue());
                } else if (value instanceof Date) {
                    cell.setCellValue(sdf.format(value));
                } else {
                    cell.setCellValue(value != null ? value.toString() : "");
                }
            }
        }
        // for (int i = 0; i < headers.size(); i++) {
        //     sheet.autoSizeColumn(i);
        // }
        return workbook;
    }

    public static List<Map<String, Object>> excel2Data(InputStream in, List<String> headers) throws Exception {
        try (Workbook workbook = new XSSFWorkbook(in)) {
            Sheet sheet = workbook.getSheetAt(0);
            List<Map<String, Object>> data = new ArrayList<>();
            int rowCount = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rowCount; i++) {
                var dataRow = sheet.getRow(i);
                Map<String, Object> rowData = new HashMap<>();
                for (int j = 0; j < headers.size(); j++) {
                    var cell = dataRow.getCell(j);
                    Object value = null;
                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case STRING -> value = cell.getStringCellValue();
                            case NUMERIC -> value = cell.getNumericCellValue();
                            case BOOLEAN -> value = cell.getBooleanCellValue();
                            case FORMULA -> value = cell.getCellFormula();
                            default -> value = null;
                        }
                    }
                    rowData.put(headers.get(j), value);
                }
                data.add(rowData);
            }
            return data;
        }
    }

    public static void exportExcel(List<String> headers, List<List<Object>> data, OutputStream out) throws Exception {
        try (Workbook workbook = data2Excel(headers, data)) {
            workbook.write(out);
        }
    }
}
